﻿using DataBaseFactoryLib;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SQLToolsCore.SQLProvide
{
    public class MSSQLControllerProvider : IControllerSQLProvider
    {
        internal BaseDataBaseOpr db = DBHelper.GetInstanse();

        public DataTable RetrieveDataStrucName(string conStr, bool tableFlag, bool procedureFlag, bool funcFlag)
        {
            string baseSQL = @"SELECT name,type,name + '(' +
            CASE WHEN LTRIM(RTRIM(type)) = 'U' THEN '数据表'
            WHEN LTRIM(RTRIM(type)) = 'P' THEN '存储过程'
            ELSE '函数' END + ')'stName FROM sys.objects
            WHERE 1 = 1 {0}
            ORDER BY CHARINDEX(LTRIM(RTRIM(type)), 'UPFN '),name;";

            string sqlCon = string.Empty;
            if (!string.IsNullOrWhiteSpace(conStr))
            {
                sqlCon += "AND replace(name,'_','') LIKE '%" + conStr.Replace("_", "") + "%'";
            }
            string typeTemp = string.Empty;
            if (tableFlag)
            {
                typeTemp = typeTemp.Concat("'U'");
            }
            if (procedureFlag)
            {
                typeTemp = typeTemp.Concat("'P'");
            }
            if (funcFlag)
            {
                typeTemp = typeTemp.Concat("'FN'");
            }
            sqlCon += string.Format("AND LTRIM(RTRIM(type)) IN ({0})", typeTemp);

            return db.QueryDataTable(string.Format(baseSQL, sqlCon));
        }

        public DataTable GetTableStructue(string tableName)
        {
            string baseSQL = $@"SELECT ROW_NUMBER()OVER(ORDER BY tb.column_id) 序号,
                   tb.name 字段名,
                   CASE
                       WHEN (tb.is_ansi_padded = 1
                            OR tb.scale > 0 
					        AND tb.data_type <> 'datetime') THEN
                           UPPER(tb.data_type) + '(' + tb.col_len + ')'
                       ELSE
                           UPPER(tb.data_type)
                   END 数据类型,
                   tb.is_null 空否,
                   CASE
                       WHEN tb.default_val IS NULL THEN
                           ''
                       WHEN LEFT(tb.default_val, 1) = '''' THEN
                           RIGHT(tb.default_val, LEN(tb.default_val) - 1)
                       ELSE
                           tb.default_val
                   END 默认值,
            CASE
               WHEN CHARINDEX('(', CAST(ISNULL(tb.value, '') AS NVARCHAR(256))) > 0 THEN
                   LEFT(CAST(ISNULL(tb.value, '') AS NVARCHAR(256)), CHARINDEX(
                                                                                  '(',
                                                                                  CAST(ISNULL(tb.value, '') AS NVARCHAR(256))
                                                                              ) - 1)
               ELSE
                   CAST(ISNULL(tb.value, '') AS NVARCHAR(256))
           END 别名,
           CASE
               WHEN CHARINDEX('(', CAST(ISNULL(tb.value, '') AS NVARCHAR(256))) > 0 THEN
                   SUBSTRING(
                                CAST(ISNULL(tb.value, '') AS NVARCHAR(256)),
                                CHARINDEX('(', CAST(ISNULL(tb.value, '') AS NVARCHAR(256))) + 1,
                                len(CAST(ISNULL(tb.value, '') AS NVARCHAR(256))) 
                                - CHARINDEX(')', reverse(CAST(ISNULL(tb.value, '') AS NVARCHAR(256))))
                                - CHARINDEX('(', CAST(ISNULL(tb.value, '') AS NVARCHAR(256)))
                            )
               ELSE
                   ''
           END 备注,
--                   ISNULL(tb.value, '') 别名,
--                   '' 备注
			tb.is_primary_key 主键
            FROM
            (
                SELECT a.column_id,
                       a.name,
                       c.scale,
                       c.name data_type,
                       SUBSTRING(d.definition, 3, LEN(d.definition) - 4) default_val,
                       CASE
                           WHEN a.is_ansi_padded = 1 THEN
                               CAST(a.max_length AS NVARCHAR(8))
                           ELSE
                               CAST(a.precision AS NVARCHAR(8)) + ',' + CAST(a.scale AS NVARCHAR(8))
                       END col_len,
                       a.is_ansi_padded,
                       CASE
                           WHEN a.is_nullable = 1 THEN
                               'Y'
                           ELSE
                               'N'
                       END is_null,
                       b.value,
					   CASE WHEN indexcol.index_column_id is null then 'N' else 'Y' end is_primary_key
                FROM sys.columns a
					LEFT JOIN sys.indexes indexs
						ON indexs.object_id = a.object_id AND indexs.is_primary_key = 1
					LEFT JOIN sys.index_columns indexcol
						ON indexcol.object_id = a.object_id AND indexcol.index_column_id = a.column_id AND indexcol.index_id = indexs.index_id
                    LEFT OUTER JOIN sys.extended_properties b
                        ON b.major_id = a.object_id
                           AND b.minor_id = a.column_id
                           AND b.name = 'MS_Description'
                    LEFT OUTER JOIN sys.types c
                        ON a.system_type_id = c.system_type_id
                    LEFT OUTER JOIN sys.default_constraints d
                        ON a.default_object_id = d.object_id
                WHERE a.object_id = OBJECT_ID('{tableName}')
                      AND c.name <> 'SYSNAME'
            ) tb;";

            return db.QueryDataTable(baseSQL);
        }
    }
}
